package com.benyi.util;

import java.io.File;
import java.io.FileInputStream;
import java.util.Map;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.lang.reflect.Field;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Excel {
	public static final String XLS = ".xls";
	public static final String XLSX = ".xlsx";

	public static List<List<Map<String, String>>> read(String file) throws Exception {
		Workbook workbook = null;
		File f = new File(file);
		if (f.exists() == false) {
			return null;
		}
		file = file.toLowerCase();
		if (file.endsWith(XLS)) {
			workbook = new HSSFWorkbook(new FileInputStream(f));
		} else {
			if (file.endsWith(XLSX)) {
				workbook = new XSSFWorkbook(new FileInputStream(f));
			} else {
				return null;
			}
		}
		List<List<Map<String, String>>> book = new ArrayList<List<Map<String, String>>>();
		Sheet sheet = null;
		
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
			sheet = workbook.getSheetAt(i);
			if (sheet == null) {
				break;
			}
			List<Map<String, String>> page = new ArrayList<Map<String, String>>();
			
			String[] keys = null;
			for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {
				Row row = sheet.getRow(j);
				if (row == null) {
					break;
				}
				if (j == 0) { // 是标题, 作为 key
					keys = new String[row.getLastCellNum()];
				}
				HashMap<String, String> line = new HashMap<String, String>();
				for (int k = 0; k < keys.length; k++) {
					Cell c=row.getCell(k);
					String v=null;
					if(c!=null){
						v=c.toString();
					}
					if (j == 0) {
						keys[k] = v;
					} else {
						line.put(keys[k], v);
					}
				}
				if (j > 0) {
					page.add(line);
				}
			}
			if (keys != null && page.size() == 0) { // 如果只有一行标题
				HashMap<String, String> line = new HashMap<String, String>();
				for (int k = 0; k < keys.length; k++) {
					line.put(keys[k], null);
				}
			}
			book.add(page);
		}
		workbook.close();
		
		return book;
	}

	public static List<Map<String, String>> read(String file, String sheetName, int titleRow) throws Exception {
		Workbook workbook = null;
		File f = new File(file);
		if (f.exists() == false) {
			return null;
		}
		file = file.toLowerCase();
		if (file.endsWith(XLS)) {
			workbook = new HSSFWorkbook(new FileInputStream(f));
		} else {
			if (file.endsWith(XLSX)) {
				workbook = new XSSFWorkbook(new FileInputStream(f));
			} else {
				return null;
			}
		}

		Sheet sheet = workbook.getSheet(sheetName);
		if (sheet == null) {
			workbook.close();
			return null;
		}

		ArrayList<Map<String, String>> page = new ArrayList<Map<String, String>>();
		String[] keys = null;
		for (int j = titleRow; j < sheet.getLastRowNum() + 1; j++) {
			Row row = sheet.getRow(j);
			if (row == null) {
				break;
			}
			if (j == titleRow) { // 是标题, 作为 key
				keys = new String[row.getLastCellNum()];
			}
			HashMap<String, String> line = new HashMap<String, String>();
			for (int k = 0; k < keys.length; k++) {
				Cell c=row.getCell(k);
				String v=null;
				if(c!=null){
					v=c.toString();
				}
				if (j == titleRow) {
					keys[k] = v;
				} else {
					line.put(keys[k], v);
				}
			}
			if ( j > titleRow ) {
				page.add(line);
			}
		}
		if (keys != null && page.size() == 0) { // 如果只有一行标题
			HashMap<String, String> line = new HashMap<String, String>();
			for (int k = 0; k < keys.length; k++) {
				line.put(keys[k], null);
			}
			page.add(line);//加标题
		}
		workbook.close();
		
		return page;
	}
	
	private static Object newObject(Class c) throws Exception{
		Object o=c.newInstance();
		return o;
	}
	
	private static void setValue(Object d,String fieldName,String v) throws IllegalArgumentException, IllegalAccessException{
		Class c=d.getClass();
		Field[] fs=c.getDeclaredFields();
		for(int i=0;i<fs.length;i++){
			if(fs[i].getName().equalsIgnoreCase(fieldName)){
				fs[i].setAccessible(true);
				Class t=fs[i].getType();
				fs[i].set(d, convert(v,t));
				break;
			}
		}
	}
	
	/**
	 * 本方法有缺陷
	 * @param v
	 * @param t
	 * @return
	 */
	private static Object convert(String v,Class t){
		if(v==null){
			return null;
		}
		if(t==String.class){
			return v;
		}
		if(t==Integer.class || t==int.class){
			return Integer.parseInt(v);
		}
		if(t==Long.class || t==long.class){
			return Long.parseLong(v);
		}
		if(t==Short.class || t==short.class){
			return Short.parseShort(v);
		}
		if(t==Float.class || t==float.class){
			return Float.parseFloat(v);
		}
		if(t==Double.class || t==double.class){
			return Double.parseDouble(v);
		}
		if(t==Boolean.class || t==boolean.class){
			return Boolean.parseBoolean(v);
		}
		if(t==char.class){
			if(v.length()==0){
				return null;
			}
			return v.charAt(0);
		}
		if(t==Date.class){
			Date d=new Date(v);
			return d;
		}
		
		return null;
	}
	
	public static List<Object> read(String file, String sheetName, int titleRow,Object m,String []fields) throws Exception {
		
		Workbook workbook = null;
		File f = new File(file);
		if (f.exists() == false) {
			return null;
		}
		file = file.toLowerCase();
		if (file.endsWith(XLS)) {
			workbook = new HSSFWorkbook(new FileInputStream(f));
		} else {
			if (file.endsWith(XLSX)) {
				workbook = new XSSFWorkbook(new FileInputStream(f));
			} else {
				return null;
			}
		}

		Sheet sheet = workbook.getSheet(sheetName);
		if (sheet == null) {
			workbook.close();
			return null;
		}

		ArrayList<Object> page = new ArrayList<Object>();
		String[] keys = null;
		for (int j = titleRow; j < sheet.getLastRowNum() + 1; j++) {
			Row row = sheet.getRow(j);
			if (row == null) {
				break;
			}
			if (j == titleRow) { // 是标题, 作为 key
				keys = new String[row.getLastCellNum()];
			}
			
			Object r=newObject(m.getClass());
			 
			for (int k = 0; k < keys.length; k++) {
				Cell c=row.getCell(k);
				String v=null;
				if(c!=null){
					v=c.toString();
				}
				if (j == titleRow) {
					keys[k] = v;
				} else {
					setValue(r,fields[k],v);
				}
			}
			if (j > titleRow ) {
				page.add(r);
			}
		}
		workbook.close();
		return page;
	}
	
	
	public static MySheet readSheet(String file, String sheetName, int titleRow) throws Exception {
		
		Workbook workbook = null;
		File f = new File(file);
		if (f.exists() == false) {
			return null;
		}
		file = file.toLowerCase();
		if (file.endsWith(XLS)) {
			workbook = new HSSFWorkbook(new FileInputStream(f));
		} else {
			if (file.endsWith(XLSX)) {
				workbook = new XSSFWorkbook(new FileInputStream(f));
			} else {
				return null;
			}
		}

		Sheet sheet = workbook.getSheet(sheetName);
		if (sheet == null) {
			workbook.close();
			return null;
		}
		MySheet page=new MySheet();
		page.setName(sheetName);
		
		String[] keys = null;
		int from=0;
		if(titleRow>=0){
			from=titleRow+1;
			Row row = sheet.getRow(titleRow);
			if(row!=null){
				keys = new String[row.getLastCellNum()];
				for (int k = 0; k < keys.length; k++){
					Cell c=row.getCell(k);
					String v=null;
					if(c!=null){
						v=c.toString();
					} 
					keys[k] = v;
				}
				page.setTitle(keys);
			}
		} 
		
		for (int j = from; j < sheet.getLastRowNum() + 1; j++) {
			Row row = sheet.getRow(j);
			if (row == null) {
				break;
			}
			String []r=null;
			if( page.getTitle() != null ){
				r = new String[page.getTitle().length];
			}else{
				r=new String[row.getLastCellNum()];
			}
			
			for (int k = 0; k < r.length; k++) {
				Cell c=row.getCell(k);
				String v=null;
				if(c!=null){
					v=c.toString();
					r[k]=v;
				}
			}
			page.addRow(r);
		}
		workbook.close();
		return page;
	}
	 
}
